﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace 定时执行上传存储过程1._0
{
    public partial class Form1 : Form
    {
        

        public Form1()
        {
            InitializeComponent();
           
        }
        #region "声明变量"

        /// <summary>
        /// 写入INI文件
        /// </summary>
        /// <param name="section">节点名称[如[TypeName]]</param>
        /// <param name="key">键</param>
        /// <param name="val">值</param>
        /// <param name="filepath">文件路径</param>
        /// <returns></returns>
        [System.Runtime.InteropServices.DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filepath);
        /// <summary>
        /// 读取INI文件
        /// </summary>
        /// <param name="section">节点名称</param>
        /// <param name="key">键</param>
        /// <param name="def">值</param>
        /// <param name="retval">stringbulider对象</param>
        /// <param name="size">字节大小</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retval, int size, string filePath);

        private string strFilePath = Application.StartupPath + "\\xc.ini";//获取INI文件路径
        private string strSec = ""; //INI文件名

        #endregion

        #region "自定义读取INI文件中的内容方法"
        /// <summary>
        /// 自定义读取INI文件中的内容方法
        /// </summary>
        /// <param name="Section">键</param>
        /// <param name="key">值</param>
        /// <returns></returns>
        private string ContentValue(string Section, string key)
        {

            StringBuilder temp = new StringBuilder(1024);
            GetPrivateProfileString(Section, key, "", temp, 1024, strFilePath);
            return temp.ToString();
        }
        #endregion

        #region "获取sqlconn连接参数"

        private string sqlconn;

        public string Sqlconn
        {
            get { return sqlconn; }
            set { sqlconn = value; }
        }

        public void getsqlconn()
        {
            if (File.Exists(strFilePath))//读取时先要判读INI文件是否存在
            {

                //strSec = Path.GetFileNameWithoutExtension(strFilePath);
                strSec = "Connection";
                string Database = ContentValue(strSec, "Database");
                string ServerName = ContentValue(strSec, "ServerName");
                string LogId = ContentValue(strSec, "LogId");
                string Logpass = ContentValue(strSec, "Logpass");
                sqlconn = @"server=" + ServerName + ";database = " + Database + "; uid =" + LogId + "; pwd = " + Logpass + ";";
            }
        }
        #endregion

        private void Form1_Load(object sender, EventArgs e)
        {
            nowTheTime.Text = "本机当前时间：" + DateTime.Now.ToString();
           
         
            if (File.Exists(strFilePath))//读取时先要判读INI文件是否存在
            {

                //strSec = Path.GetFileNameWithoutExtension(strFilePath);
                strSec = "Connection";
               comboBox1.Text = ContentValue(strSec, "time");
               textBox1.Text = ContentValue(strSec, "fClub");
               textBox2.Text = ContentValue(strSec, "zonDBname");
               timer1.Interval = Convert.ToInt32(ContentValue(strSec, "time")) * 60000;
               comboBox2.Text = ContentValue(strSec, "hour");
               comboBox3.Text = ContentValue(strSec, "minute");
               DateTime dt = DateTime.Now.Date.AddHours(Convert.ToInt32(ContentValue(strSec, "hour"))).AddMinutes(Convert.ToInt32(ContentValue(strSec, "minute")));
               label10.Text = dt.ToString();
               if (ContentValue(strSec, "model") == "1")
               {
                   radioButton1.Checked = true;
                   comboBox1.Enabled = false;
                   groupBox2.Enabled = true;
               }
               else
               {
                   radioButton2.Checked = true;
                   comboBox2.Enabled = false;
                   comboBox3.Enabled = false;
                  
               }
            }

            if (radioButton2.Checked == true)
            {
                // string date = DateTime.Now.Date.ToString();
                label6.Text = "间隔模式：工作中！";
              
                timer1.Start();
            }
           
            timer2.Start();

           
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                //根据INI文件名设置要写入INI文件的节点名称
                //此处的节点名称完全可以根据实际需要进行配置
                strSec = "Connection";
                WritePrivateProfileString(strSec, "time", comboBox1.Text.Trim(), strFilePath);
                WritePrivateProfileString(strSec, "fClub", textBox1.Text.Trim(), strFilePath);
                WritePrivateProfileString(strSec, "hour", comboBox2.Text.Trim(), strFilePath);
                WritePrivateProfileString(strSec, "minute", comboBox3.Text.Trim(), strFilePath);
                WritePrivateProfileString(strSec, "zonDBname", textBox2.Text.Trim(), strFilePath);
                if (radioButton1.Checked == true)
                {
                    WritePrivateProfileString(strSec, "model", "1", strFilePath);
                }
                else
                {
                    WritePrivateProfileString(strSec, "model", "2", strFilePath);
                }
               
                MessageBox.Show("保存成功");
                DateTime dt = DateTime.Now.Date.AddHours(Convert.ToInt32(ContentValue(strSec, "hour"))).AddMinutes(Convert.ToInt32(ContentValue(strSec, "minute")));
                label10.Text = dt.ToString();

                if (radioButton2.Checked == true)
                {
                    timer1.Interval = Convert.ToInt32(comboBox1.Text.Trim()) * 60000;
                    timer1.Start();
                    label6.Text = "间隔模式：工作中！";
                }
                else
                {
                    //timer1.Interval = Convert.ToInt32(comboBox1.Text.Trim()) * 60000;
                    timer1.Stop();
                    label6.Text = "间隔模式：已停止！";
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString());

            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //getsqlconn();
            //string ConStr = sqlconn;
            //SqlConnection conn; 
            //SqlCommand cmd;
            //conn = new SqlConnection(ConStr);
            //string cmdText = "prc_get_fow";
            //DataSet ds = new DataSet();             //创建参数列表,并为参数赋值 
            //SqlParameter[] paras = new SqlParameter[] 
            //{ new SqlParameter("@calling", "123") 

            //};
            //cmd = new SqlCommand(cmdText, conn); 
            //cmd.Parameters.AddRange(paras);
            //cmd.CommandType = CommandType.StoredProcedure;//表明Command对象类型为存储过程 
            //SqlDataAdapter sdp = new SqlDataAdapter(); 
            //sdp.SelectCommand = cmd; 
            //sdp.Fill(ds);
            //dataGridView1.DataSource = ds;
           
            up();
            //timer1.Start();
           
            

        }

        private void notifyIcon1_MouseClick(object sender, MouseEventArgs e)
        {
            //双击显示主窗口
            if (e.Button == MouseButtons.Left)
            {
                this.Show();
                ShowInTaskbar = true;
                this.WindowState = FormWindowState.Normal;
            }
        }

        private void Form1_SizeChanged(object sender, EventArgs e)
        {
            //隐藏窗口
            if (this.WindowState == FormWindowState.Minimized)
            {
                this.Hide();
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            
            if (radioButton2.Checked == true)
            {
                label6.Text = "间隔模式：工作中！";
                up();
            }
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            nowTheTime.Text = "本机当前时间：" + DateTime.Now.ToString();
            //定时执行上传
            //如果当前时间为设置时间则执行
            if (radioButton1.Checked==true&&DateTime.Now.ToString() == label10.Text)
            {
                if (rbt_up.Checked == true)
                {
                    up();
                }
                else
                {
                    System.Diagnostics.Process mp = new System.Diagnostics.Process();
                    mp.StartInfo.FileName = "cmd.exe";
                    mp.StartInfo.RedirectStandardInput = true;
                    mp.StartInfo.RedirectStandardOutput = true;
                    mp.StartInfo.RedirectStandardError = true;
                    mp.StartInfo.UseShellExecute = false;
                    mp.StartInfo.CreateNoWindow = true; mp.Start();
                    mp.StandardInput.WriteLine("shutdown -s -t 0");//关机
                }
            }
        }

        public void up()
        {
            getsqlconn();
            string ConStr = sqlconn;
            SqlConnection conn;
            conn = new SqlConnection(ConStr);
            SqlCommand sqlCmd = new SqlCommand("p_up_table_all", conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程  
            sqlCmd.CommandTimeout = 50000;
            try
            {


                SqlParameter sqlParme;
                //参数1  
                sqlParme = sqlCmd.Parameters.Add("@fclub", SqlDbType.NVarChar);
                sqlParme.Direction = ParameterDirection.Input;
                sqlParme.Value = textBox1.Text.Trim();
                //参数2(在多了就以此类推)  
                //sqlParme = sqlCmd.Parameters.Add("@called", SqlDbType.NVarChar);
                //sqlParme.Direction = ParameterDirection.Input;
                //sqlParme.Value = "345";
                //sqlParme = sqlCmd.Parameters.Add("@o_called", SqlDbType.NVarChar);
                //sqlParme.Direction = ParameterDirection.Input;
                //sqlParme.Value = "346";
                conn.Open();
                int result = sqlCmd.ExecuteNonQuery();
                //首先判断列表框中的项是否大于0
                if (listBox1.Items.Count > 100)
                {
                    //移出选择的项
                    listBox1.Items.Clear();
                }
                if (result > 0)
                {
                    listBox1.Items.Add(nowTheTime.Text + "成功上传" + result.ToString() + "行。");
                }
                else
                {
                     listBox1.Items.Add(nowTheTime.Text + "无上传记录，影响到" + result.ToString() + "行。");
                   // MessageBox.Show("影响到" + result.ToString() + "行!");
                }
            }
            catch (Exception ex)
            {
                timer1.Stop();
                label6.Text = "间隔模式：已停止！";
                listBox1.Items.Add(nowTheTime.Text + ">>未知错误，定时器失效！");
                listBox1.Items.Add(ex.Message);
                //MessageBox.Show(ex.Message);
                
            }
            finally
            {
                conn.Close();
            }
        }

        #region //根据传入的sql语句返回受影响的行数

        public int exec_sql(string sql)
        {
            int result = 0;
            getsqlconn();
            SqlConnection conn = new SqlConnection(Sqlconn);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                conn.Close();
            }
            if (result > 0)
            {
                MessageBox.Show("更新成功！影响到" + result.ToString() + "行！\n请点击执行上传插入丢失的记录。");
            }
            return result;
        }
        #endregion

        #region //查询丢失的记录数
        private  int ExcuteScalarSQL(string sql)
        {
            int i=0;
            getsqlconn();
            SqlConnection conn = new SqlConnection(Sqlconn);
            try
            {
               
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return i;
        }
        #endregion

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            if (radioButton1.Checked == true)
            {
                comboBox1.Enabled = false;
                comboBox2.Enabled = true;
                comboBox3.Enabled = true;
                groupBox2.Enabled = true;
            }
            else
            {
                comboBox1.Enabled = true;
                comboBox2.Enabled = false;
                comboBox3.Enabled = false;
                groupBox2.Enabled = false;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            double a, b, c, d;
            a= ExcuteScalarSQL("select  COUNT(*) from dbo.pos_bills a where  not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bills b where a.timestampflag=b.timestampflag)");
            b = ExcuteScalarSQL("select  COUNT(*) from dbo.pos_sales a where  not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_sales b where a.timestampflag=b.timestampflag)");
            c = ExcuteScalarSQL("select  COUNT(*) from dbo.pos_bill_payment a where  not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bill_payment b where a.timestampflag=b.timestampflag)");
            d = ExcuteScalarSQL("select  COUNT(*) from dbo.fitness_checkin a where  not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.fitness_checkin b where a.timestampflag=b.timestampflag)");
            if (a > 0)
            {
                
                if (MessageBox.Show("订单主表记录丢失数：" + a +"\n是否更新丢失的记录?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {

                    exec_sql("update pos_bills  set fclub=fclub where not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bills b where pos_bills.timestampflag=b.timestampflag)");

                }
            }
            else  if (b > 0)
            {
                if (MessageBox.Show("订单从表记录丢失数：" + b + "\n是否更新丢失的记录?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {
                    exec_sql("update pos_sales  set fclub=fclub where not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bills b where pos_sales.timestampflag=b.timestampflag)");
                   

                }
            }
            else if (c > 0)
            {
                if (MessageBox.Show("付款方式表记录丢失数：" + c + "\n是否更新丢失的记录?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {

                    exec_sql("update pos_bill_payment  set fclub=fclub where not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bills b where pos_bill_payment.timestampflag=b.timestampflag)");

                }
            }
            else if (d > 0)
            {
                if (MessageBox.Show("票务表记录丢失数：" + d + "\n是否更新丢失的记录?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {

                    exec_sql("update fitness_checkin  set fclub=fclub where not exists(select 1  from fsletsserver."+textBox2.Text+".dbo.pos_bills b where fitness_checkin.timestampflag=b.timestampflag)");

                }
            }
            else
            {
                MessageBox.Show("未检查到数据丢失！");
            }
        }

       

       

       
    }
}
